﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Framework;

namespace Enterprise
{
    /// <summary>
    /// 页面管理
    /// </summary>
    public class PageService : BaseService
    {
        /// <summary>
        /// 获取页面列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(string title, int columnId, string language, int pageValue, int rowsValue)
        {
            var parameters = new[]
                {
                    new SqlParameter("@CoTitle", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoLanguage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoColumnId", SqlDbType.Int)
                };
            parameters[0].Value = title.SqlLike();
            parameters[1].Value = language.SqlLike();
            parameters[2].Value = columnId;
            var sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append(" where A.CoTitle like '%'+@CoTitle+'%' and A.CoLanguage like '%'+@CoLanguage+'%' ");
            if (columnId != 0)
            {
                sbSqlWhere.Append(" and A.CoColumnId=@CoColumnId");
            }
            int total = DbHelperSql.GetSingle("select count(*) from TbPage as A {0}".FormatWith(sbSqlWhere), parameters).SafeToInt();
            var sbSql = new StringBuilder();
            sbSql.AppendFormat("select top {0} A.CoPageId,A.CoTitle,A.CoColumnId,A.CoIsRecommend,A.CoIsPopular,A.CoIsTop,A.CoViews,A.CoAuthor,A.CoDate,A.CoIsReserved,A.CoReservedUrl,A.CoLanguage, B.CoName as CoCategoryName from TbPage as A left join TbColumn as B on  A.CoColumnId=B.CoColumnId {1} ", rowsValue, sbSqlWhere);
            if (pageValue > 1)
                sbSql.AppendFormat(" and A.CoPageId not in (select top {0} A.CoPageId from TbPage as A {1} order by A.CoPageId) ", rowsValue * (pageValue - 1), sbSqlWhere);
            sbSql.Append(" order by A.CoPageId");
            var ds = DbHelperSql.Query(sbSql.ToString(), parameters);
            var table = new Table
            {
                Rows = ds.Tables["ds"].Rows,
                Total = total
            };
            return table;
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            return DbHelperSql.Query("select * from TbPage where CoPageId={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }
        public int Add(int columnId, string title, string content, string summary, string author, string language, string reservedUrl, DateTime date, bool isRecommend, bool isReserved, bool isPopular, bool isTop, string keyWord, string description)
        {
            Check.Argument.IsNotOutOfLength(title, 2, 50, "页面标题");
            Check.Argument.IsNotOutOfLength(summary, 252, "页面简介");
            Check.Argument.IsNotOutOfLength(author, 20, "作者");
            Check.Argument.IsNotOutOfLength(keyWord, 50, "页面关键字");
            Check.Argument.IsNotOutOfLength(description, 80, "页面描述");
            Check.Argument.IsNotOutOfLength(language, 20, "页面语言");
            Check.Argument.IsNotOutOfLength(reservedUrl, 252, "转载地址");
            Check.Argument.IsNotInvalidSqlDateTime(date, "发布日期");
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("insert into TbPage(");
            strSqlOne.Append("CoColumnId,CoColumnPath,CoTitle,CoContent,CoSummary,CoReservedUrl,CoAuthor,CoLanguage,CoDate,CoIsReserved,CoIsPopular,CoIsTop,CoIsRecommend,CoViews,CoKeyWord,CoDescription");
            strSqlOne.Append(")");
            strSqlOne.Append(" values (");
            strSqlOne.Append("@CoColumnId,@CoColumnPath,@CoTitle,@CoContent,@CoSummary,@CoReservedUrl,@CoAuthor,@CoLanguage,@CoDate,@CoIsReserved,@CoIsPopular,@CoIsTop,@CoIsRecommend,@CoViews,@CoKeyWord,@CoDescription");
            strSqlOne.Append(")");
            SqlParameter[] parametersOne =
                {
                    new SqlParameter("@CoColumnId", SqlDbType.Int),
                    new SqlParameter("@CoColumnPath", SqlDbType.Text),
                    new SqlParameter("@CoTitle", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoContent", SqlDbType.Text),
                    new SqlParameter("@CoSummary", SqlDbType.Text),
                    new SqlParameter("@CoReservedUrl", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoAuthor", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoLanguage", SqlDbType.VarChar, 10),
                    new SqlParameter("@CoDate", SqlDbType.DateTime),
                    new SqlParameter("@CoIsReserved",SqlDbType.Bit),
                    new SqlParameter("@CoIsPopular",SqlDbType.Bit),
                    new SqlParameter("@CoIsTop",SqlDbType.Bit),
                    new SqlParameter("@CoIsRecommend",SqlDbType.Bit),  
                    new SqlParameter("@CoViews", SqlDbType.Int),
                    new SqlParameter("@CoKeyWord", SqlDbType.VarChar,255),
                    new SqlParameter("@CoDescription", SqlDbType.VarChar,255)

                };
            using (var sdr = DbHelperSql.ExecuteReader("select * from TbColumn where CoColumnId={0}".FormatWith(columnId)))
            {
                if (sdr.Read())
                {
                    parametersOne[0].Value = columnId;
                    parametersOne[1].Value = sdr["CoColumnPath"].SafeToString();
                }
                else
                {
                    parametersOne[0].Value = DBNull.Value;
                    parametersOne[1].Value = "";
                }
            }
            parametersOne[2].Value = title;
            parametersOne[3].Value = content;
            parametersOne[4].Value = summary;
            parametersOne[5].Value = reservedUrl;
            parametersOne[6].Value = author;
            parametersOne[7].Value = language;
            parametersOne[8].Value = date;
            parametersOne[9].Value = isReserved;
            parametersOne[10].Value = isPopular;
            parametersOne[11].Value = isTop;
            parametersOne[12].Value = isRecommend;
            parametersOne[13].Value = 0;
            parametersOne[14].Value = keyWord;
            parametersOne[15].Value = description;
            var obj = DbHelperSql.Add(strSqlOne.ToString(), parametersOne).SafeToInt();
            return obj;
        }
        public int Edit(int id, int columnId, string title, string content, string summary, string author, string language, string reservedUrl, DateTime date, bool isRecommend, bool isReserved, bool isPopular, bool isTop, string keyWord, string description)
        {
            Check.Argument.IsNotOutOfLength(title, 2, 50, "页面标题");
            Check.Argument.IsNotOutOfLength(summary, 252, "页面简介");
            Check.Argument.IsNotOutOfLength(author, 20, "作者");
            Check.Argument.IsNotOutOfLength(language, 20, "页面语言");
            Check.Argument.IsNotOutOfLength(reservedUrl, 252, "转载地址");
            Check.Argument.IsNotInvalidSqlDateTime(date, "发布日期");
            var sbSql = new StringBuilder();
            sbSql.Append("update TbPage set ");
            sbSql.Append("CoColumnId=@CoColumnId");
            sbSql.Append(",");
            sbSql.Append("CoColumnPath=@CoColumnPath");
            sbSql.Append(",");
            sbSql.Append("CoTitle=@CoTitle");
            sbSql.Append(",");
            sbSql.Append("CoContent=@CoContent");
            sbSql.Append(",");
            sbSql.Append("CoSummary=@CoSummary");
            sbSql.Append(",");
            sbSql.Append("CoReservedUrl=@CoReservedUrl");
            sbSql.Append(",");
            sbSql.Append("CoAuthor=@CoAuthor");
            sbSql.Append(",");
            sbSql.Append("CoLanguage=@CoLanguage");
            sbSql.Append(",");
            sbSql.Append("CoDate=@CoDate");
            sbSql.Append(",");
            sbSql.Append("CoIsReserved=@CoIsReserved");
            sbSql.Append(",");
            sbSql.Append("CoIsPopular=@CoIsPopular");
            sbSql.Append(",");
            sbSql.Append("CoIsTop=@CoIsTop");
            sbSql.Append(",");
            sbSql.Append("CoIsRecommend=@CoIsRecommend");
            sbSql.Append(",");
            sbSql.Append("CoKeyWord=@CoKeyWord");
            sbSql.Append(",");
            sbSql.Append("CoDescription=@CoDescription");
            sbSql.Append(" where CoPageId=@id;");
            SqlParameter[] parameters =
                {
                    new SqlParameter("@CoColumnId", SqlDbType.Int),
                    new SqlParameter("@CoColumnPath", SqlDbType.Text),
                    new SqlParameter("@CoTitle", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoContent", SqlDbType.Text),
                    new SqlParameter("@CoSummary", SqlDbType.Text),
                    new SqlParameter("@CoReservedUrl", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoAuthor", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoLanguage", SqlDbType.VarChar, 10),
                    new SqlParameter("@CoDate", SqlDbType.DateTime),
                    new SqlParameter("@CoIsReserved", SqlDbType.Bit),
                    new SqlParameter("@CoIsPopular", SqlDbType.Bit),
                    new SqlParameter("@CoIsTop", SqlDbType.Bit),
                    new SqlParameter("@CoIsRecommend", SqlDbType.Bit),
                    new SqlParameter("@CoKeyWord", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoDescription", SqlDbType.VarChar, 255),
                    new SqlParameter("@id", SqlDbType.Int)
                };
            using (var sdr = DbHelperSql.ExecuteReader("select * from TbColumn where CoColumnId={0}".FormatWith(columnId)))
            {
                if (sdr.Read())
                {
                    parameters[0].Value = columnId;
                    parameters[1].Value = sdr["CoColumnPath"].SafeToString();
                }
                else
                {
                    parameters[0].Value = DBNull.Value;
                    parameters[1].Value = "";
                }
            }
            parameters[2].Value = title;
            parameters[3].Value = content;
            parameters[4].Value = summary;
            parameters[5].Value = reservedUrl;
            parameters[6].Value = author;
            parameters[7].Value = language;
            parameters[8].Value = date;
            parameters[9].Value = isReserved;
            parameters[10].Value = isPopular;
            parameters[11].Value = isTop;
            parameters[12].Value = isRecommend;
            parameters[13].Value = keyWord;
            parameters[14].Value = description;
            parameters[15].Value = id;
            var obj = DbHelperSql.ExecuteSql(sbSql.ToString(), parameters).SafeToInt();
            return obj;

        }
        public void Delete(int id)
        {
            DbHelperSql.ExecuteSql("delete from TbPage where CoPageId={0}".FormatWith(id));
        }

    }
}
